#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
修复Excel测试数据文件
"""

import pandas as pd
import os
from datetime import datetime

def fix_excel_data():
    """修复Excel测试数据文件的问题"""
    excel_file = "tests/data/example_test_cases.xlsx"
    
    if not os.path.exists(excel_file):
        print(f"Excel文件不存在: {excel_file}")
        return False
    
    try:
        # 备份原文件
        backup_file = f"{excel_file}.backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
        import shutil
        shutil.copy2(excel_file, backup_file)
        print(f"已备份原文件到: {backup_file}")
        
        # 读取所有工作表
        xl_file = pd.ExcelFile(excel_file, engine='openpyxl')
        
        # 处理每个工作表
        sheets_data = {}
        
        for sheet_name in xl_file.sheet_names:
            print(f"\n处理工作表: {sheet_name}")
            df = pd.read_excel(excel_file, sheet_name=sheet_name, engine='openpyxl')
            
            # 修复字段名问题
            if 'expected_status' in df.columns:
                df = df.rename(columns={'expected_status': 'expected_status_code'})
                print(f"  - 已将 'expected_status' 重命名为 'expected_status_code'")
            
            # 根据工作表类型补充数据
            if sheet_name == '用户管理测试':
                # 补充用户测试数据
                for idx, row in df.iterrows():
                    if pd.isna(row.get('name')):
                        if 'TC001' in str(row.get('test_case_id', '')):
                            df.at[idx, 'name'] = 'John Doe'
                            df.at[idx, 'email'] = 'john.doe@example.com'
                            df.at[idx, 'age'] = 30
                        elif 'TC002' in str(row.get('test_case_id', '')):
                            df.at[idx, 'name'] = 'Jane Smith'
                            df.at[idx, 'email'] = 'jane.smith@example.com'
                            df.at[idx, 'age'] = 25
                        elif 'TC003' in str(row.get('test_case_id', '')):
                            df.at[idx, 'name'] = 'Bob Johnson'
                            df.at[idx, 'email'] = 'bob.johnson@example.com'
                            df.at[idx, 'age'] = 35
                        elif 'TC004' in str(row.get('test_case_id', '')):
                            df.at[idx, 'name'] = 'Alice Brown'
                            df.at[idx, 'email'] = 'alice.brown@example.com'
                            df.at[idx, 'age'] = 28
                        elif 'TC005' in str(row.get('test_case_id', '')):
                            df.at[idx, 'name'] = 'Charlie Wilson'
                            df.at[idx, 'email'] = 'charlie.wilson@example.com'
                            df.at[idx, 'age'] = 32
                        elif 'TC006' in str(row.get('test_case_id', '')):
                            df.at[idx, 'name'] = 'Diana Davis'
                            df.at[idx, 'email'] = 'diana.davis@example.com'
                            df.at[idx, 'age'] = 27
                print(f"  - 已补充用户测试数据")
            
            # 确保所有测试用例都有expected_status_code字段
            if 'expected_status_code' not in df.columns:
                df['expected_status_code'] = 200  # 默认值
                print(f"  - 已添加 'expected_status_code' 字段，默认值为 200")
            else:
                # 填充空值
                df['expected_status_code'] = df['expected_status_code'].fillna(200)
                print(f"  - 已填充 'expected_status_code' 字段的空值")
            
            sheets_data[sheet_name] = df
            print(f"  - 工作表 '{sheet_name}' 处理完成")
        
        # 创建新的Excel文件
        with pd.ExcelWriter(excel_file, engine='openpyxl') as writer:
            for sheet_name, df in sheets_data.items():
                df.to_excel(writer, sheet_name=sheet_name, index=False)
        
        print(f"\n✅ Excel文件修复完成: {excel_file}")
        return True
        
    except Exception as e:
        print(f"❌ 修复Excel文件时出错: {e}")
        return False

def verify_fix():
    """验证修复结果"""
    excel_file = "tests/data/example_test_cases.xlsx"
    
    try:
        xl_file = pd.ExcelFile(excel_file, engine='openpyxl')
        print(f"\n=== 验证修复结果 ===")
        
        total_valid = 0
        total_cases = 0
        
        for sheet_name in xl_file.sheet_names:
            print(f"\n工作表: {sheet_name}")
            df = pd.read_excel(excel_file, sheet_name=sheet_name, engine='openpyxl')
            
            # 检查必填字段
            required_fields = ['test_case_id', 'test_name', 'api_method', 'api_path', 'expected_status_code']
            
            valid_cases = 0
            for _, row in df.iterrows():
                is_valid = True
                for field in required_fields:
                    if field not in df.columns or pd.isna(row.get(field)):
                        is_valid = False
                        break
                if is_valid:
                    valid_cases += 1
            
            total_valid += valid_cases
            total_cases += len(df)
            
            print(f"  有效测试用例: {valid_cases}/{len(df)}")
            print(f"  跳过测试用例: {len(df) - valid_cases}")
        
        print(f"\n总计:")
        print(f"  有效测试用例: {total_valid}/{total_cases}")
        print(f"  修复成功率: {(total_valid/total_cases)*100:.1f}%")
        
        return total_valid > 0
        
    except Exception as e:
        print(f"验证时出错: {e}")
        return False

if __name__ == "__main__":
    print("开始修复Excel测试数据文件...")
    
    if fix_excel_data():
        verify_fix()
    else:
        print("修复失败")